package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;
import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
import forms.formAddReviewer;
import forms.formDecision;
import forms.formDeleteReviewer;
import forms.formReview;
import forms.formUpdateReviewer;

public class reviewerModel {

	private static String selectStr = "SELECT user_name FROM user_roles WHERE role='reviewer'";
	private static String addReviewer = "INSERT INTO users (user_name,password,email) VALUES (?, md5(?), ?)";
	private static String addReviewerRole = "INSERT INTO user_roles (user_ref,user_name,role) VALUES (?,?,?)";
	private static String updateStr = "UPDATE users SET user_name = ?, password = ?, email = ?, WHERE user_name = ?";
	private static String deleteStr = "DELETE FROM users WHERE user_name = ?";
	private static String deleteStrRole = "DELETE FROM user_roles WHERE user_name = ?";

	public static CachedRowSet getAllReviewer() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectStr);
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allReviewer = pStmt.executeQuery();

			CachedRowSet crsReviewer = new CachedRowSetImpl();
			crsReviewer.populate(allReviewer);

			allReviewer.close();
			pStmt.close();
			conn.close();

			return crsReviewer;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static void addReviewer(formAddReviewer reviewer) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();
			PreparedStatement pStmt = conn.prepareStatement(addReviewer);

			pStmt.setString(1, reviewer.getUsername());
			pStmt.setString(2, reviewer.getPassword());
			pStmt.setString(3, "");
			pStmt.executeUpdate();

			pStmt = conn.prepareStatement(addReviewerRole);
			Statement stmt = conn.createStatement();
			// ResultSet cause DB exception
			ResultSet rs = stmt
					.executeQuery("SELECT user_id from users WHERE user_name ='"
							+ reviewer.getUsername() + "'");
			rs.next();
			pStmt.setInt(1, rs.getInt(1));
			pStmt.setString(2, reviewer.getUsername());
			pStmt.setString(3, "reviewer");
			pStmt.executeUpdate();
			// Commit transaction
			conn.commit();

			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static void updateReviewer(formUpdateReviewer reviewer)
			throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			Statement stmt = conn.createStatement();
			//ResultSet rs = stmt.executeQuery("SELECT user_name FROM user_roles WHERE role='reviewer'");
			//rs.next();
			PreparedStatement pStmt = conn.prepareStatement("UPDATE users SET user_name = ?, password = ?, email = ?, WHERE user_name ='"+reviewer.getUsername()+"'");

			pStmt.setString(1, reviewer.getUsername());
			pStmt.setString(2, reviewer.getPassword());
			pStmt.setString(3, "");
			pStmt.executeUpdate();

			// Commit transaction
			conn.commit();

			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static void deleteReviewer(formDeleteReviewer reviewer)
			throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the prepared statement and use it to
			// DELETE students FROM the Students table.
			PreparedStatement pStmt = conn.prepareStatement(deleteStr);
			pStmt.setString(1, reviewer.getUserName());
			pStmt.executeUpdate();
			
			pStmt = conn.prepareStatement(deleteStrRole);
			pStmt.setString(1, reviewer.getUserName());
			pStmt.executeUpdate();

			// Commit transaction
			conn.commit();
			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	public static void addGradeResult(formReview reviewer)
	throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();
			
			PreparedStatement pStmt = conn.prepareStatement("INSERT INTO grade_result (student_id,grade,comment) VALUES (?,?,?)");
			pStmt.setInt(1, Integer.parseInt(reviewer.getId()));
			pStmt.setString(2, reviewer.getGrade());
			pStmt.setString(3, reviewer.getComment());
			pStmt.executeUpdate();
			pStmt = conn.prepareStatement("UPDATE list_application SET graded='true' WHERE student_id='"+reviewer.getId()+"'");
			pStmt.executeUpdate();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT user_id FROM user_link WHERE student_id='"+reviewer.getId()+"'");
			rs.next();
			int userId = rs.getInt(1);
			rs = stmt.executeQuery("SELECT average FROM student_status WHERE user_id='"+userId+"'");
			rs.next();
			String value = rs.getString(1);
			if(value.equals("0"))
			{
				pStmt = conn.prepareStatement("UPDATE student_status SET average='"+reviewer.getGrade()+"' WHERE user_id='"+userId+"'" );
				pStmt.executeUpdate();
			}
			else
			{
				rs = stmt.executeQuery("SELECT grade FROM grade_result WHERE student_id='"+reviewer.getId()+"'");
				int total = 0;
				int count = 0;
				if(rs != null)
					while(rs.next())
					{
						count++;
						total += Integer.parseInt(rs.getString(1));
					}
				double newAverage = (double)total/(double)count;
				pStmt = conn.prepareStatement("UPDATE student_status SET average='"+Double.toString(newAverage)+"' WHERE user_id='"+userId+"'");
				pStmt.executeUpdate();
			}
			conn.commit();
			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
}
